package com.nuist.springmybatis.mapper;

import com.nuist.springmybatis.model.UserInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserInfoMapper {
    @ResultMap(value = "resultMap")

    @Select("select username, 'password', age,gender,phone from userinfo")
    List<UserInfo> queryAllUsers();

    @Select("select * from userinfo where delete_flag = #{deleteFlag}")
    List<UserInfo> getUserInfoByDeleteFlag(Integer deleteFlag);

    @Select("select * from userinfo where delete_flag = '${deleteFlag}'")
    List<UserInfo> getUserInfoByDeleteFlag2(Integer deleteFlag);

    @Select("select * from userinfo where username = #{userName}")
    UserInfo getUserByName(String userName);

    @Select("select * from userinfo where username = '${userName}'")
    UserInfo getUserByName2(String userName);

    @Select("select * from userinfo where delete_flag = #{Flag} and gender= #{gender}")
    List<UserInfo> getUserInfo2(@Param("Flag") Integer deleteFlag, Integer gender);

    @Select("select * from userinfo where id = #{id}")
    UserInfo getUserInfo3(Integer id);

    //插入 Insert
    @Insert("insert into userinfo (username,password,age,gender) " +
            "values (#{userInfo.username},#{userInfo.password},#{userInfo.age},#{userInfo.gender})")
    Integer insert(@Param("userInfo") UserInfo userInfo);

    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("insert into userinfo (username, password, age, gender)" +
            " values (#{username},#{password},#{age},#{gender})")
    Integer insert2(UserInfo userInfo);

    @Delete("delete from userinfo where id = #{id}")
    Integer delete(Integer id);

    @Update("update userinfo set password = #{password}, age = #{age}, gender = #{gender} " +
            "where id = #{id}")
    Integer update(UserInfo userInfo);

    /**
     * 1. 起别名
     */
    @Select("SELECT id, username, password, age, gender, phone, "
            + "delete_flag as deleteFlag, create_time as createTime, update_time as updateTime " +
            "FROM `userinfo`")
    List<UserInfo> selectUserInfos();

    /**
     * 2. 指定结果映射关系
     */
    @Results(id = "resultMap", value = {
            @Result(column = "delete_flag", property = "deleteFlag"),
            @Result(column = "create_time", property = "createTime"),
            @Result(column = "update_time", property = "updateTime")})
    @Select("SELECT * FROM `userinfo`")
    List<UserInfo> selectUserInfos2();

    @ResultMap("resultMap")
    @Select("SELECT * FROM `userinfo`")
    List<UserInfo> selectUserInfos3();


    /*
    *
    * 模拟SQL注入
    * */
    @Select("select * from userinfo where username = #{userName}")
    UserInfo selectUserByName(String userName);

    @Select("select * from userinfo where username = '${userName}'")
    UserInfo selectUserByName2(String userName);
}
